Charts and Series

Setting Chart Type

The chart_type attribute of the Chart object or the ChartType property of the Chart object can be used to set the type of a chart. For a chart object cht, the chart type can be set in the following ways.

【Excel VBA】

cht.ChartType = xlColumnClustered

【Python xlwings】

>>> cht.chart_type = 'column_clustered'

【Python xlwings API】

>>> cht.ChartType = xw.constants.ChartType.xlColumnClustered

The values of the chart_type attribute or ChartType property are shown in Table 6-1. The third column of Table 6-1 represents the string value of the chart type as the value for the chart_type attribute in the xlwings way, while the constants or values in the first two columns are used as the values for the ChartType property in the Excel VBA and API ways. Values can be written directly; the constant form is similar to xw.constants.ChartType.xlLine.

Table 6-1 Excel Chart Types

Next, we will create more types of charts using the AddChart2 method of the Shapes object with the data provided in Section 6.1.

【Excel VBA】

The path of the sample file is Samples\ch18\Excel VBA\Chart Types.xlsm.

Sub CreateCharts()
    ActiveSheet.Range("A1").CurrentRegion.Select  ' Data
    ActiveSheet.Shapes.AddChart2 -1, xlColumnClustered, _
        20, 150, 300, 200, True
    ActiveSheet.Shapes.AddChart2 -1, xlBarClustered, _
        400, 150, 300, 200, True
    ActiveSheet.Shapes.AddChart2 -1, xlConeBarStacked, _
        20, 400, 300, 200, True
    ActiveSheet.Shapes.AddChart2 -1, xlLineMarkersStacked, _
        400, 400, 300, 200, True
    ActiveSheet.Shapes.AddChart2 -1, xlXYScatter, _
        20, 650, 300, 200, True
    ActiveSheet.Shapes.AddChart2 -1, xlPieOfPie, _
        400, 650, 300, 200, True
End Sub

Run the procedure to generate different types of charts as shown in Figure 6-5.

Figure 6-5

【Python xlwings API】

The path of the Python script file is Samples\ch18\Python\Chart Types.py.

(The preceding code is omitted; please refer to the Python file.)

... ...
sht.api.Range('A1').CurrentRegion.Select()  # Data
sht.api.Shapes.AddChart2(-1, xw.constants.ChartType.xlColumnClustered, \
    20, 150, 300, 200, True)
sht.api.Shapes.AddChart2(-1, xw.constants.ChartType.xlBarClustered, \
    400, 150, 300, 200, True)
sht.api.Shapes.AddChart2(-1, xw.constants.ChartType.xlConeBarStacked, \
    20, 400, 300, 200, True)
sht.api.Shapes.AddChart2(-1, xw.constants.ChartType.xlLineMarkersStacked, \
    400, 400, 300, 200, True)
sht.api.Shapes.AddChart2(-1, xw.constants.ChartType.xlXYScatter, \
    20, 650, 300, 200, True)
sht.api.Shapes.AddChart2(-1, xw.constants.ChartType.xlPieOfPie, \
    400, 650, 300, 200, True)

Run the script to generate different types of charts as shown in Figure 6-5.

Common Properties and Methods of Chart Object

The type of a chart is set using the ChartType property of the Chart object. In fact, the Chart object has many other properties and methods that can be used to configure the chart in various ways. The commonly used properties of the Chart object are listed in Table 6-2, which will be introduced gradually later.

Table 6-2: Commonly Used Properties of the Chart Object

Setting Series

Each Chart object has a SeriesCollection property, which returns a collection containing all the series in the chart. So what is a series? For the composite column chart shown in Figure 6-3, each province corresponds to one composite column, and each composite column contains six single columns of different colors. All single columns of the same color across provinces form a series. Therefore, the composite column chart shown in Figure 6-3 has six series in total. A series can be represented by a Series object.

Figure 6-6: Chart Generated by Default

[Excel VBA]

The sample file path is Samples\ch18\Excel VBA\Sequence.xlsm.

Sub Test()
    'Create a chart
    ActiveSheet.Range("A1:B7").Select
    ActiveSheet.Shapes.AddChart
End Sub

Running this procedure generates the chart shown in Figure 6-6.

[Python xlwings API]

The Python script file path is Samples\ch18\Python\SetSeries.py.

Previous code omitted; refer to the Python file.

sht.api.Range("A1:B7").Select()
cht = sht.api.Shapes.AddChart().Chart

Running the script generates the chart shown in Figure 6-6. The first line selects the data for plotting, and the second line uses the AddChart method of the Shapes object to create a Shape object representing the chart. The Chart property of this object returns a Chart object.

We can use the SeriesCollection property of the Chart object to return a collection containing all the series in the chart. Below, we use the Count property to get the number of series in the collection.

[Excel VBA]

Sample file path: Samples\ch18\Excel VBA\Sequence.xlsm.

Sub Test2()
    Dim cht As Chart
    ActiveSheet.Range("A1:B7").Select
    Set cht = ActiveSheet.Shapes.AddChart.Chart
    Debug.Print cht.SeriesCollection.Count
End Sub

Running this procedure outputs the number of series in the chart (i.e., 2) in the Immediate Window.

[Python xlwings API]

Code to get the number of series in the chart using Python xlwings API:

>>> cht.SeriesCollection().Count
2

As shown in Figure 6-6, there are two types of columns (different colors), and each color forms a series—hence two series in total.

We can reference a series by its name or index in the collection. Below, we reference the second series, change its chart type to a line chart using the ChartType property, enable smoothing with the Smooth property, set the marker at each data point to a triangle using MarkerStyle, set the marker color to blue with MarkerForegroundColor, and display data labels by setting HasDataLabel to True.

[Excel VBA]

Sample file path: Samples\ch18\Excel VBA\Sequence.xlsm.

Sub Test3()
    Dim cht As Chart
    Dim ser2 As Series
    ActiveSheet.Range("A1:B7").Select
    Set cht = ActiveSheet.Shapes.AddChart.Chart
    Set ser2 = cht.SeriesCollection("P2")    'Second series
    ser2.ChartType = xlLine                  'Line chart
    ser2.Smooth = True                       'Smoothing
    ser2.MarkerStyle = xlMarkerStyleTriangle 'Marker
    ser2.MarkerForegroundColor = RGB(0, 0, 255) 'Color(blue)
    ser2.HasDataLabels = True                'Display data labels
End Sub

Running this procedure produces the effect shown in Figure 6-7 (after setting the second series).

Figure 6-7

[Python xlwings API]

Python script path: Samples\ch18\Python\SetSeries.py.

Previous code omitted; refer to the Python file.

ser2 = cht.SeriesCollection('P2')               #Second series
ser2.ChartType = xw.constants.ChartType.xlLine  #Line chart
ser2.Smooth = True                              #Smoothing
ser2.MarkerStyle = xw.constants.MarkerStyle.xlMarkerStyleTriangle  #Marker
ser2.MarkerForegroundColor = xw.utils.rgb_to_int((0, 0, 255))       #Color(blue)
ser2.HasDataLabels = True                       #Display data labels

Running the script changes the chart to the effect in Figure 6-7. This shows that individual series can be modified by setting properties of the Series object.

Setting Properties of Individual Points in a Series

Using the Points property of the Series object, we can get all data points in the series. We can extract specific points via an index and configure them. An individual point is represented by a Point object, whose properties and methods allow us to customize the specified point. Point settings are mainly used for line charts, scatter plots, radar charts, etc.

Continuing from Section 6.2.3, we first get the number of data points in the second series.

[Excel VBA]

Sample file path: Samples\ch18\Excel VBA\Sequence.xlsm.

Sub Test3()
    'Omit previous code; refer to the sample file
    '......
    Debug.Print ser2.Points.Count
End Sub

Running this procedure outputs the number of data points in the second series (i.e., 6) in the Immediate Window.

[Python xlwings API]

Code to get the number of data points in the second series using Python xlwings API:

>>> ser2.Points().Count
6

Common properties of the Point object are listed in Table 6-3.

Table 6-3: Common Properties of the Point Object

The MarkerStyle property of the Point object sets the marker style. Its value is of the xlMarkerStyle enumeration type (see Table 6-4).

Table 6-4: Values of the MarkerStyle Property

Below, we modify the properties of the third point in the line chart representing the second series: set its foreground/background color to blue, marker style to diamond, and marker size to 10 points.

[Excel VBA]

Sample file path: Samples\ch18\Excel VBA\Sequence.xlsm.

Sub Test4()
    Dim cht As Chart
    Dim ser2 As Series
    ActiveSheet.Range("A1:B7").Select
    Set cht = ActiveSheet.Shapes.AddChart.Chart
    Set ser2 = cht.SeriesCollection("P2")       'Second series
    ser2.ChartType = xlLine                     'Line chart
    ser2.Smooth = True                          'Smoothing
    ser2.MarkerStyle = xlMarkerStyleTriangle    'Marker
    ser2.MarkerForegroundColor = RGB(0, 0, 255)'Color(blue)
    ser2.HasDataLabels = True                   'Display data labels
    'Modify the third point
    ser2.Points(3).MarkerForegroundColor = RGB(0, 0, 255)
    ser2.Points(3).MarkerBackgroundColor = RGB(0, 0, 255)
    ser2.Points(3).MarkerStyle = xlMarkerStyleDiamond
    ser2.Points(3).MarkerSize = 10
End Sub

Running this procedure generates the chart in Figure 6-8. After configuration, the third point in the series is highlighted.

Figure 6-8

[Python xlwings API]

Python script path: Samples\ch18\Python\SetPointInSeries.py.

Previous code omitted; refer to the Python file.

ser2.Points(3).MarkerForegroundColor = xw.utils.rgb_to_int((0, 0, 255))
ser2.Points(3).MarkerBackgroundColor = xw.utils.rgb_to_int((0, 0, 255))
ser2.Points(3).MarkerStyle = xw.constants.MarkerStyle.xlMarkerStyleDiamond
ser2.Points(3).MarkerSize = 10

Running the script produces the final effect in Figure 6-8.